#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from lxml import etree
import openpyxl


xml_path = r'C:\Users\xiaoyao\Desktop\newobj.xml'
excel_path = r'C:\Users\xiaoyao\Desktop\newobj.xlsx'

wb = openpyxl.load_workbook(excel_path)
ws = wb['Sheet1']
xml_tree = etree.parse(open(xml_path, 'r', encoding='utf-8'))
xml_item_list = xml_tree.getroot()

keys_list = []
for item in xml_item_list:
    item_attr = item.attrib
    for k, v in item_attr.items():
        if k not in keys_list:
            keys_list.append(k)

column = 1
for key in keys_list:
    ws.cell(row=1, column=column).value = key
    column += 1

row = 2
for item in xml_item_list:
    item_attr = item.attrib
    for k, v in item_attr.items():
        key_column = keys_list.index(k) + 1
        ws.cell(row=row, column=key_column).value = v
    row += 1

wb.save(excel_path)
